package org.grapes.wine.api.server.database;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.grapes.wine.api.server.exception.MethodException;
import org.grapes.wine.api.server.param.MethodConfig;

@Deprecated
public class ReadData {
	
	@Deprecated
	public static List<Map> readTable(String sqlStatement, String placeHolder)
			throws Exception, MethodException {
		Connection conn = null;
		String embeddedDriver = "org.apache.derby.jdbc.EmbeddedDriver";
		String user = DerbyConfig.user;
		String password = DerbyConfig.password;
		String embeddedURL = "jdbc:derby:wineDB;user=" + user + ";password="
				+ password + ";";
		Class.forName(embeddedDriver);
		conn = DriverManager.getConnection(embeddedURL);
		Statement stmt = conn.createStatement();

		List<Map> list = null;
		list = execute(stmt, MethodConfig.READ_SQL, sqlStatement, placeHolder);
		conn.close();
		return list;
	}
	
	@Deprecated
	public static List<Map> updateTable(String sqlStatement, String placeHolder)
			throws Exception, MethodException {
		Connection conn = null;
		String embeddedDriver = "org.apache.derby.jdbc.EmbeddedDriver";
		String user = DerbyConfig.user;
		String password = DerbyConfig.password;
		String embeddedURL = "jdbc:derby:wineDB;user=" + user + ";password="
				+ password + ";";
		Class.forName(embeddedDriver);
		conn = DriverManager.getConnection(embeddedURL);
		Statement stmt = conn.createStatement();

		String query = callStatement(MethodConfig.UPDATE_SQL, sqlStatement,
				placeHolder);
		
		List<Map> list = new ArrayList<Map>();
		try {
			stmt.executeUpdate(query);
			Map map = new HashMap();
			map.put("description", "sql insert/update/delete is successful");
			list.add(map);
		} catch (SQLException e) {
			throw new MethodException(e);
		}

		conn.close();
		return list;
	}

	private static List<Map> execute(Statement stmt, String methodName,
			String sqlStatement, String placeHolder) throws SQLException,
			IOException, MethodException {
		List<Map> result = new ArrayList<Map>();

		String query = callStatement(methodName, sqlStatement, placeHolder);
		ResultSet resultSet = null;
		try {
			if (stmt.execute(query)) {
					resultSet = stmt.getResultSet();
				result = ResultSetToListMap(resultSet);
			}
		} catch (SQLException e) {
			throw new MethodException(e);
		}
		resultSet.close();
		return result;
	}

	/*
	 * transform ResultSet to List<Map>
	 */
	private static List<Map> ResultSetToListMap(ResultSet rs)
			throws SQLException {
		List<Map> result = null;
		int start = rs.getRow();
		rs.last();
		int rowCount = rs.getRow();
		rs.beforeFirst();
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		Map rowMap = null;
		result = new ArrayList<Map>();

		int m = 1;
		while (rs.next()) {
			rowMap = new HashMap<String, Object>();
			for (int i = 1; i <= columnCount; i++) {
				String columnName = rsmd.getColumnName(i).toLowerCase();
				rowMap.put(columnName, rs.getObject(i));
			}
			result.add(rowMap);
		}
		return result;
	}

	/*
	 * construct a SQL call statement
	 */
	private static String callStatement(String sqlMethodName,
			String sqlStatement, String placeHolder) {
		String callStmt = "call " + sqlMethodName + "('" + sqlStatement + "','"
				+ placeHolder + "')";
		return callStmt;
	}
}